#!/usr/bin/python

#
# Copyright 2012 Google Inc. All Rights Reserved.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.
#
# This code is not supported by Google
#

"""
Sample python connector to feed merchantcenter product information to a GSA

*Downloads product catalog data from merchantcenter and upload uploads it to the GSA as
an incremental feed.
*Each itemID/URL is saved to a sqlite3 database
*Performs DELETE operations on items missing from subsequent traversals

to use
setup:
sudo apt-get update
sudo apt-get install sqlite3 python-libxml2
download connectormanager.py and connector.py from:
http://code.google.com/p/gsa-admin-toolkit/wiki/ConnectorManagerDocumentation
then
./connectormanager.py --debug --connectors=merchantcenter_connector.MerchantCenterConnector
On the GSA, register the connectormanager then configure a merchantcenter connector
with: 
 <merchantcenter username>
 <merchantcenter password> 
 <merchant center ID>
 <Delay: traversal interval in seconds>
"""

import connector
import datetime
import urllib,urlparse,urllib2
import sys
import time
import libxml2
import rfc822
from xml.sax.saxutils import escape
from xml.sax.saxutils import quoteattr
import zlib
import base64
import string
import re
import sqlite3
import httplib, ssl, socket
from dateutil.parser import parse
from dateutil import tz

class MerchantCenterConnector(connector.TimedConnector):

  CONNECTOR_TYPE = 'merchantcenter-connector'
  CONNECTOR_CONFIG = {
      'username': { 'type': 'text', 'label': 'MerchantCenter username' },
      'password': { 'type': 'password', 'label': 'MerchantCenter password' },
      'merchant_id': { 'type': 'text', 'label': 'MerchantCenter ID' },
      'delay': { 'type': 'text', 'label': 'Fetch Delay' }
  }

  def init(self):    
    self.setInterval(int(self.getConfigParam('delay')))
    self.USER = self.getConfigParam('username')
    self.PASSWD = self.getConfigParam('password')
    self.CID = self.getConfigParam('merchant_id')

  def run(self):

    self.logger().info('TIMER START')
    
    try:
      self.conn = sqlite3.connect('itemstatus_'+self.CID+'.sqlite',check_same_thread = False)
      self.cursor = self.conn.cursor()
    except sqlite3.connect:
      self.logger().error("ERROR Connecting to DB")  

    try:
      self.cursor.execute('CREATE TABLE mc_item (id text primary key unique, cid text, link text, canonical_id text, last_sync DATETIME, expiration_date DATETIME)')
      self.cursor.execute('create index idx_id ON mc_item(id);')  
      self.cursor.execute('create index idx_last_sync ON mc_item(last_sync);') 
      #self.cursor.execute('create index idx_expiration ON mc_item(expiration_date);') 
    except sqlite3.OperationalError, e:
      if (str(e)!='table mc_item already exists'):
        self.logger().error(str(e))
        
    auth_token = self.__getAuthToken(self.USER,self.PASSWD)
    self.now = time.mktime(datetime.datetime.now().timetuple())    
    self.__getProducts(auth_token, self.CID)
    self.__removeProducts(self.now)
    if (self.conn != None):
      self.conn.close()

    self.logger().info('TIMER END')
    
  def __getAuthToken(self, email, passwd):
      #import gdata.client;
      #client = gdata.client.GDClient();
      #client.ClientLogin(email,passwd,"Test","structuredcontent")
      #authToken = client.auth_token.token_string   
            
      authURL = "https://www.google.com/accounts/ClientLogin"
      params = urllib.urlencode({'Email': email, 'Passwd': passwd, 'service': 
                                 'structuredcontent', 'source': 'Test'})
      headers = {"Content-type": "application/x-www-form-urlencoded"}
      urllib2.install_opener(urllib2.build_opener(HTTPSHandlerV3()))
      req = urllib2.Request(authURL, params, headers)
      try:
        response = urllib2.urlopen(req)
      except urllib2.HTTPError, e:
          self.log('Unable to acquire AuthToken.')
          self.log('Error code: ' + str(e))
          sys.exit(1)
      except urllib2.URLError, e:
          self.logger().error('Failed to connect to auth server.')
          self.logger().error( 'Reason: ' + str(e.reason ))
          sys.exit(1)
      lresponse = response.read()
      authResponseDict = dict(x.split("=") for x in lresponse.split("\n") if x)
      if authResponseDict["Auth"] is None:
        self.logger().error("Unable to acquire token")
        sys.exit(1)
      authToken = authResponseDict["Auth"] 
      self.logger().info("Acquired Token:" + authToken)
      return authToken
        
  def __getProducts(self, token, cid):
    
    baseURL = ("https://content.googleapis.com/content/v1/" + cid + 
               "/items/products/generic?max-results=200")
    urllib2.install_opener(urllib2.build_opener(HTTPSHandlerV3()))
    has_next_link = True;
    num_items_processed = 0
    while (has_next_link):   
      has_next_link = False;
      self.logger().debug("Recalling: " + baseURL) 
      req = urllib2.Request(baseURL)      
      req.add_header("Authorization", "GoogleLogin auth=" + token)
  
      try:
          response = urllib2.urlopen(req)
      except urllib2.HTTPError, e:
          self.logger().error('The server couldnt fulfill the request.')
          self.logger().error('Error code: ' + str(e.code))
          sys.exit(1)
      except urllib2.URLError, e:
          self.logger().error('We failed to reach a server.')
          self.logger().error( 'Reason: '+ str(e.reason))
          sys.exit(1)
  
      content = response.read()    
      doc = libxml2.parseDoc(content)
      ctxt = doc.xpathNewContext()
      ctxt.xpathRegisterNs('openSearch', "http://a9.com/-/spec/opensearch/1.1/")
      ctxt.xpathRegisterNs('s', "http://www.google.com/shopping/api/schemas/2010")  
      ctxt.xpathRegisterNs('atom', "http://www.w3.org/2005/Atom")  
      ctxt.xpathRegisterNs('app', "http://www.w3.org/2007/app")
      ctxt.xpathRegisterNs('gd', "http://schemas.google.com/g/2005")
      ctxt.xpathRegisterNs('sc', "http://schemas.google.com/structuredcontent/2009")
      ctxt.xpathRegisterNs('scp', "http://schemas.google.com/structuredcontent/2009/products")
         
      nl_links = ctxt.xpathEval('/atom:feed/atom:link')
      for n_link in nl_links:
        rel = n_link.xpathEval('@rel')[0].content
        if (rel == "next"):
          has_next_link = True;
          baseURL = n_link.xpathEval('@href')[0].content 
                              
      nl_offers = ctxt.xpathEval('/atom:feed/atom:entry')
      items = ''
      for n_offer in nl_offers:         
        ctxt.setContextNode(n_offer) 
        image_link = None   
        title = ctxt.xpathEval('atom:title')[0].content
        provided_id = ctxt.xpathEval('sc:id')[0].content  
        enlisted_usecases = []
        required_usecases = ['CommerceSearch', 'ProductSearch']
        nl_usecases = ctxt.xpathEval("app:control/sc:status[@status='enlisted']")
        for n_usecase in nl_usecases:
          enlisted_usecases.append(n_usecase.prop('dest'))
        skip_offer = True
        for c in required_usecases:
          if c in enlisted_usecases:
            skip_offer = False
        if skip_offer:
          self.log("Not enlisted for requried usecase(s). Skipping product " + provided_id)        
        if (len(ctxt.xpathEval('atom:content'))>0):
          description = ctxt.xpathEval('atom:content')[0].content
        else:   
          description = ''
        canonical_id = urllib2.unquote(ctxt.xpathEval('atom:id')[0].content)        
        expiration_date = ctxt.xpathEval('sc:expiration_date')[0].content  
        if len(ctxt.xpathEval('sc:image_link')) > 0:
          image_link = escape(ctxt.xpathEval('sc:image_link')[0].content) 
        
        t = time.mktime(datetime.datetime.strptime(ctxt.xpathEval('atom:updated')[0].content, "%Y-%m-%dT%H:%M:%S.%fZ").timetuple())
        last_modified = rfc822.formatdate ( rfc822.mktime_tz( rfc822.parsedate_tz( datetime.datetime.fromtimestamp(float(t) ).strftime ( "%a, %d %b %Y %H:%M:%S" ) ) ) )
        nl_links = ctxt.xpathEval('atom:link')        
        for n_link in nl_links:
          if n_link.prop('rel') == 'alternate':
            # append the providedID to the url to ensure unique URLs
            original_link = n_link.prop('href')
            params = {'pid':provided_id}
            link = escape(self.addGetParameters(original_link, params))
                    
        meta = '<metadata>'
        nl_attributes = ctxt.xpathEval('sc:attribute')        
        for n_attribute in nl_attributes:
          ctxt.setContextNode(n_attribute)
          v = self.__remove_control_chars_and_escape(ctxt.xpathEval('.')[0].content)
          if (v!='' and n_attribute.prop('name') == 'brand'):
            meta = meta + '<meta name="' + n_attribute.prop('name').replace(' ','_') + '" content='  + v + '/>'          
          elif (n_attribute.prop('name') ==  'product_type'):
            y = 1
            curr = ''
            c = v.split('>')
            for jj in c:
              if curr != '':
                curr = curr + ' > ' + jj.replace('"','').strip()
              else:
                curr = jj.replace('"','').strip()
              meta = meta +  '<meta name="product_type_' + str(y) + '" content='  + self.remove_control_chars_and_escape(jj.replace('"','').strip()) + '/>'
              meta = meta +  '<meta name="product_type_level_' + str(y) + '" content='  + self.remove_control_chars_and_escape(curr) + '/>'
              y = y+1              
              
        meta = meta + '<meta name="title" content=' + self.__remove_control_chars_and_escape(title) + '/>' 
        meta = meta + '<meta name="id" content="' + escape(provided_id) + '"/>' 
        if image_link != None:
          meta = meta + '<meta name="image_link" content="' + escape(image_link) + '"/>'
        if description != '':
          meta = meta + '<meta name="description" content=' + self.__remove_control_chars_and_escape(description) + '/>'            
        meta = meta + '<meta name="expiration_date" content="' + escape(expiration_date) + '"/>'         
        meta = meta + '</metadata>'                      
        pr = self.__getPageRank(original_link,provided_id)
        if not skip_offer:
          items = items + '<record pagerank="' + str(pr) + '" url="' + link + '" displayurl="' + original_link + '" last-modified="' + last_modified + '" mimetype="text/html" action="add" lock="true">'
          cc = '<html><head><title>%s</title></head><body>%s<body></html>'%(title,description)
          #content = base64.b64encode(zlib.compress(cc))
          #items = items + meta + '<content encoding="base64compressed">' +  content +'</content>'
          items = items + meta + '<content><![CDATA[' +  cc +']]></content>'             
          items = items + '</record>'
          u_expiration_time = time.mktime(parse(expiration_date).timetuple())
          self.__updateItemStatus(provided_id,cid,original_link,canonical_id,self.now,u_expiration_time)          
        num_items_processed = num_items_processed + 1        
      ctxt.xpathFreeContext()  
      doc.freeDoc()
      self.pushRaw(data=items, feed_type='incremental')
      self.conn.commit()        
      self.logger().info("Number of items processed: " + str(num_items_processed))
      
  def __getPageRank(self,link,provided_id):
    return 98
  
  #http://stackoverflow.com/questions/2506379/add-params-to-given-url-in-python
  def addGetParameters(self,url, newParams):
      (scheme, netloc, path, params, query, fragment) = urlparse.urlparse(url)
      queryList = urlparse.parse_qsl(query, keep_blank_values=True)
      for key in newParams:
          queryList.append((key, newParams[key]))
      return urlparse.urlunparse((scheme, netloc, path, params, urllib.urlencode(queryList), fragment))  

  def __updateItemStatus(self,provided_id,merchant_id,link, canonical_id, sync_time, expiration_time):
    try:
      stmt = ('INSERT OR REPLACE INTO  mc_item (id, cid, link, canonical_id,last_sync, expiration_date) VALUES ("%s","%s", "%s", "%s", "%s","%s")')%(provided_id, merchant_id,link,canonical_id,sync_time, expiration_time)
      self.cursor.execute(stmt)
    except sqlite3.OperationalError, e:
      self.logger().error(str(e))    

  def __removeProducts(self,now):
    entry = '' 
    try:
      for row in self.cursor.execute('select id,link from mc_item where last_sync <' + str(now) ):
        item_to_delete = '<record url="%s" action="delete"/>\n' % row[1]
        entry = entry + item_to_delete
        self.logger().info ('Deleting item ' + row[0])
      #for row in self.cursor.execute('select id,link from mc_item where expiration_date <' + str(now) ):
      #  item_to_delete = '<record url="%s"  mimetype="text/html" action="delete"/>\n' % row[1]
      #  entry = entry + item_to_delete
      #  self.log('Deleting Expired itemID: ' + row[0] )           
      if (entry != ''):
        self.pushRaw(data=entry, feed_type='incremental')
        self.cursor.execute('delete from mc_item where last_sync <' + str(now))
        self.conn.commit()
    except sqlite3.OperationalError, e:
      self.logger().error(str(e))   

  def __remove_control_chars_and_escape(self,s):
    return quoteattr(s)
  
class HTTPSConnectionV3(httplib.HTTPSConnection):
    def __init__(self, *args, **kwargs):
        httplib.HTTPSConnection.__init__(self, *args, **kwargs)

    def connect(self):
        sock = socket.create_connection((self.host, self.port), self.timeout)
        if self._tunnel_host:
            self.sock = sock
            self._tunnel()
        try:
            key_file = None #'cleintcert_key.pem'
            cert_file = None # 'clientcert_crt.pem'
            self.sock = ssl.wrap_socket(sock, key_file,cert_file, ssl_version=ssl.PROTOCOL_TLSv1)
        except ssl.SSLError, e:
            print("Trying SSLv23.")
            self.sock = ssl.wrap_socket(sock, key_file,cert_file, ssl_version=ssl.PROTOCOL_SSLv23)
            
class HTTPSHandlerV3(urllib2.HTTPSHandler):
  def https_open(self, req):
    return self.do_open(HTTPSConnectionV3, req)    
 
